package com.agileai.hotweb.common;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import javax.sql.DataSource;

import com.agileai.domain.db.Column;
import com.agileai.util.ListUtil;
import com.agileai.util.MapUtil;

public class DBHelper {
	private String datasourceId = "dataSource";
	private String keyGenTableName = "SYS_TABLE_PK";
	
	public Connection createConnection(){
		java.sql.Connection connection = null;
		try {
			BeanFactory beanFactory = BeanFactory.instance();
			DataSource dataSource = (DataSource)beanFactory.getBean(datasourceId);
			connection = dataSource.getConnection();
		    connection.setAutoCommit(false);
		}
		catch (Exception e) {
		    e.printStackTrace();
		}
		return connection;
	}
	public Connection createConnection(String datasourceId){
		java.sql.Connection connection = null;
		try {
			BeanFactory beanFactory = BeanFactory.instance();
			DataSource dataSource = (DataSource)beanFactory.getBean(datasourceId);
			connection = dataSource.getConnection();
		    connection.setAutoCommit(false);
		}
		catch (Exception e) {
		    e.printStackTrace();
		}
		return connection;
	}
	
	public void commitConnection(Connection connection) {
		try {
			connection.commit();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	public void rollbackConnection(Connection connection) {
		try {
			connection.rollback();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	public void releaseConnection(Connection connection) {
		try {
			if (connection != null) {
				connection.close();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	public int executeUpdateSql(Connection connection,String sql) throws SQLException{
		int result = 0;
		try {
//			System.out.println("sql is " + sql);
			result = connection.createStatement().executeUpdate(sql);	
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		}
		return result;
	}
	public int executeUpdateSql(Connection connection,String sql,Object[] params) throws SQLException{
		int result = 0;
		try {
			PreparedStatement pstmt = connection.prepareStatement(sql);
			setParams(pstmt, params);
//			System.out.println("sql is " + getPreparedSQL(sql,params));
			result = pstmt.executeUpdate();	
			pstmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		}
		return result;
	}
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public List getRecords(Connection connection,String sql) throws SQLException{
		List result = new ArrayList();
//		System.out.println("sql is " + sql);
		try {
			ResultSet rs = connection.createStatement().executeQuery(sql);
			if (rs != null){
				ResultSetMetaData metaData = rs.getMetaData();
				int count = metaData.getColumnCount();
				while (rs.next()) {
					HashMap row = new HashMap();
					result.add(row);
					for (int i=1;i <= count;i++){
						String colName = metaData.getColumnName(i);
						Object colValue = rs.getObject(colName);
						if (colValue != null){
							row.put(colName,colValue);
						}
					}
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		}
		return result;
	}
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public List getRecords(Connection connection,String sql,Object[] params) throws SQLException{
		List result = new ArrayList();
		try {
			PreparedStatement pstmt = connection.prepareStatement(sql);
			setParams(pstmt,params);
//			System.out.println("sql is " + getPreparedSQL(sql,params));
			ResultSet rs = pstmt.executeQuery();
			if (rs != null){
				ResultSetMetaData metaData = rs.getMetaData();
				int count = metaData.getColumnCount();
				while (rs.next()) {
					HashMap row = new HashMap();
					result.add(row);
					for (int i=1;i <= count;i++){
						String colName = metaData.getColumnName(i);
						Object colValue = rs.getObject(colName);
						if (colValue != null){
							row.put(colName,colValue);
						}
					}
				}
			}
			rs.close();
			pstmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		}
		return result;
	}
	
	protected String getPreparedSQL(String sql, Object[] params) {
		int paramNum = 0;
		if (null != params)
			paramNum = params.length;
		if (1 > paramNum)
			return sql;
		StringBuffer returnSQL = new StringBuffer();
		String[] subSQL = sql.split("\\?");
		for (int i = 0; i < paramNum; i++) {
			if (params[i] instanceof java.util.Date) {
				returnSQL.append(subSQL[i]).append(" '").append(params[i])
						.append("' ");
			} else {
				returnSQL.append(subSQL[i]).append(" '").append(params[i])
						.append("' ");
			}
		}
		return returnSQL.toString();
	}
	private  void setParams(PreparedStatement pstmt,Object[] params) throws SQLException {
		if (null != params) {
			int paramNum = params.length;
			for (int i = 0; i < paramNum; i++) {
				try {
					if (null != params[i]
							&& params[i] instanceof java.util.Date) {
						pstmt.setTimestamp(i + 1,converDate(params[i]));
					} else {
						if (params[i] == null){
							pstmt.setNull(i + 1,Types.NULL);
						}else{
							pstmt.setObject(i + 1,params[i]);
						}
					}
				} catch (SQLException e) {
					throw e;
				}
			}
		}
	}
	private static Timestamp converDate(Object date){
		return new Timestamp(((java.util.Date)date).getTime()); 
	}
	private String metaDataSql(String tableName){
		StringBuffer sql = new StringBuffer();
		sql.append("select * from ").append(tableName).append(" where 1=2");
		return sql.toString();
	}
	
	private ResultSet getResultSet(Connection connection,String sql) {
		ResultSet result = null;
		try {
			result = connection.createStatement().executeQuery(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}
    public  Column[] getColumns(String tableName){
    	Column[] result = null;
    	Connection connection = null;
		try {
			connection = createConnection();
	    	ResultSet rs = getResultSet(connection,metaDataSql(tableName));
	    	ResultSetMetaData rsMetaData = rs.getMetaData();
			int colCount = rsMetaData.getColumnCount();
			result = new Column[colCount];
			for (int i=1;i <= colCount;i++){
				result[i-1] = new Column();
				String colName = rsMetaData.getColumnName(i);
				String colLabel = rsMetaData.getColumnLabel(i);
				String colTypeName = rsMetaData.getColumnTypeName(i);
				String colClassName = rsMetaData.getColumnClassName(i);
				int nullable = rsMetaData.isNullable(i);
				int displaySize = rsMetaData.getColumnDisplaySize(i);
				result[i-1].setName(colName);
				result[i-1].setLabel(colLabel);
				result[i-1].setTypeName(colTypeName);
				result[i-1].setClassName(colClassName);
				
				if (0 == nullable){
					result[i-1].setNullable(false);
				}else{
					result[i-1].setNullable(true);
				}
				result[i-1].setLength(displaySize);
			}
			String catalog = connection.getCatalog();
			DatabaseMetaData dmd = connection.getMetaData();
			rs = dmd.getPrimaryKeys(catalog,null, tableName.toUpperCase());
			while(rs.next()){
				String pkColName = rs.getString("COLUMN_NAME");
				setPKColumn(result,pkColName);
			}
			connection.commit();
		} catch (Exception e) {
			rollbackConnection(connection);
			e.printStackTrace();
		}
		finally{
			releaseConnection(connection);			
		}
    	return result;
    }
    private static void setPKColumn(Column[] columns,String pkColumn){
    	for (int i=0;i < columns.length;i++){
    		Column column = columns[i];
    		if (column.getName().equals(pkColumn)){
    			column.setPK(true);
    			break;
    		}
    	}
    }
	@SuppressWarnings({"rawtypes" })
	public long getMaxId(String tableName,String colName)  {
		long result = 0;
		String sql = "select max("+colName+") as MAX_ID from "+tableName;
		Connection connection = null;
		try {
			connection = createConnection();
			List records = getRecords(connection, sql);
			if (!ListUtil.isNullOrEmpty(records)){
				HashMap row = (HashMap)records.get(0);
				if (MapUtil.isNullOrEmpty(row)){
					result = new Long(0);
				}else{
					String maxId = String.valueOf(row.get("MAX_ID"));
					result = new Long(maxId);					
				}
			}
			connection.commit();
		} catch (Exception e) {
			rollbackConnection(connection);
			e.printStackTrace();
		}
		finally{
			releaseConnection(connection);
		}
		return result;
	}
	@SuppressWarnings("rawtypes")
	public long getTablePKValue(String tableName,String pkName){
		long result = 0;
		Connection connection = null;
		try {
			String sql = "select PRK_VALUE from " + keyGenTableName 
				+" where TABLE_NAME = '"+tableName.toUpperCase()+"' and PRK_NAME = '"+pkName.toUpperCase()+"'";			
			connection = createConnection();
			List records = getRecords(connection, sql);
			if (records != null && records.size() > 0){
				HashMap row = (HashMap)records.get(0);
				String temp = String.valueOf(row.get("PRK_VALUE"));
				result = Long.parseLong(temp);
				long tempValue = ++result;
				sql = "update "+keyGenTableName+" set PRK_VALUE = " + tempValue 
				+" where TABLE_NAME = '"+tableName.toUpperCase()+"' and PRK_NAME = '"+pkName.toUpperCase()+"'";
				executeUpdateSql(connection, sql);
			}
			connection.commit();
		} catch (Exception e) {
			rollbackConnection(connection);
			e.printStackTrace();
		}
		finally{
			releaseConnection(connection);
		}
		return result;
	}	
	public void updateTablePK(String tableName,String pkName,long value){
		Connection connection = null;
		try {
			String sql = "update "+keyGenTableName+" set PRK_VALUE = " + value 
				+" where TABLE_NAME = '"+tableName.toUpperCase()+"' and PRK_NAME = '"+pkName.toUpperCase()+"'";			
			connection = createConnection();
			executeUpdateSql(connection, sql);
			connection.commit();
		} catch (Exception e) {
			rollbackConnection(connection);
			e.printStackTrace();
		}
		finally{
			releaseConnection(connection);
		}	
	}
	public  void initTablePK(String tableName,String pkName,long value){
		Connection connection = null;
		try {
			String sql = "insert into "+keyGenTableName+" (TABLE_NAME,PRK_NAME,PRK_VALUE) " +
					" values ('"+tableName.toUpperCase()+"','"+pkName.toUpperCase()+"',"+value+")";			
			connection = createConnection();
			executeUpdateSql(connection, sql);
			connection.commit();
		} catch (Exception e) {
			rollbackConnection(connection);
			e.printStackTrace();
		}
		finally{
			releaseConnection(connection);
		}	
	}
	@SuppressWarnings("rawtypes")
	public boolean isExistTablePK(String tableName){
		boolean result = false;
		Connection connection = null;
		try {
			String sql = "select TABLE_NAME from "+keyGenTableName+" where TABLE_NAME='"+tableName.toUpperCase()+"'";			
			connection = createConnection();
			List tempList = getRecords(connection, sql);
			if (tempList.size() > 0){
				result = true;
			}
			connection.commit();
		} catch (Exception e) {
			rollbackConnection(connection);
			e.printStackTrace();
		}
		finally{
			releaseConnection(connection);
		}
		return result;
	}	
}
